                                                                                                                         pg_get_functiondef                                                                                                                         
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 CREATE OR REPLACE FUNCTION public.handle_check_in(p_member_id uuid, p_name text, p_email text, p_card_id uuid, p_class_type text, p_check_in_date date, p_trainer_id uuid DEFAULT NULL::uuid, p_is_1v2 boolean DEFAULT false)                                     +
  RETURNS jsonb                                                                                                                                                                                                                                                    +
  LANGUAGE plpgsql                                                                                                                                                                                                                                                 +
 AS $function$                                                                                                                                                                                                                                                     +
 DECLARE                                                                                                                                                                                                                                                           +
   v_result JSONB;                                                                                                                                                                                                                                                 +
   v_is_extra BOOLEAN;                                                                                                                                                                                                                                             +
   v_check_in_id UUID;                                                                                                                                                                                                                                             +
   v_class_type class_type;                                                                                                                                                                                                                                        +
 BEGIN                                                                                                                                                                                                                                                             +
   -- 转换class_type                                                                                                                                                                                                                                               +
   BEGIN                                                                                                                                                                                                                                                           +
     v_class_type := p_class_type::class_type;                                                                                                                                                                                                                     +
   EXCEPTION WHEN OTHERS THEN                                                                                                                                                                                                                                      +
     RETURN jsonb_build_object(                                                                                                                                                                                                                                    +
       'success', false,                                                                                                                                                                                                                                           +
       'message', '无效的课程类型: ' || p_class_type,                                                                                                                                                                                                              +
       'error', SQLERRM                                                                                                                                                                                                                                            +
     );                                                                                                                                                                                                                                                            +
   END;                                                                                                                                                                                                                                                            +
                                                                                                                                                                                                                                                                   +
   -- 开始事务                                                                                                                                                                                                                                                     +
   BEGIN                                                                                                                                                                                                                                                           +
     -- 检查会员是否存在，不存在则创建                                                                                                                                                                                                                             +
     IF NOT check_member_exists(p_member_id) THEN                                                                                                                                                                                                                  +
       INSERT INTO members(id, name, email, is_new_member)                                                                                                                                                                                                         +
       VALUES (p_member_id, p_name, p_email, true);                                                                                                                                                                                                                +
                                                                                                                                                                                                                                                                   +
       v_is_extra := true;                                                                                                                                                                                                                                         +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 检查是否重复签到                                                                                                                                                                                                                                           +
     IF check_duplicate_check_in(p_member_id, p_check_in_date, p_class_type) THEN                                                                                                                                                                                  +
       RETURN jsonb_build_object(                                                                                                                                                                                                                                  +
         'success', false,                                                                                                                                                                                                                                         +
         'message', '今天已经在这个时段签到过了',                                                                                                                                                                                                                  +
         'isDuplicate', true                                                                                                                                                                                                                                       +
       );                                                                                                                                                                                                                                                          +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 验证会员卡                                                                                                                                                                                                                                                 +
     IF p_card_id IS NOT NULL THEN                                                                                                                                                                                                                                 +
       -- 获取会员卡信息
       SELECT * INTO v_card FROM membership_cards WHERE id = p_card_id;
       
       -- 检查会员卡是否存在
       IF NOT FOUND THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '会员卡不存在', jsonb_build_object('card_id', p_card_id), p_member_id);
       -- 检查会员卡是否属于该会员
       ELSIF v_card.member_id != p_member_id THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '会员卡不属于该会员', jsonb_build_object('card_id', p_card_id, 'card_member_id', v_card.member_id), p_member_id);
       -- 检查会员卡是否过期
       ELSIF v_card.valid_until IS NOT NULL AND v_card.valid_until < p_check_in_date THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '会员卡已过期', jsonb_build_object('card_id', p_card_id, 'valid_until', v_card.valid_until, 'check_in_date', p_check_in_date), p_member_id);
       -- 检查卡类型是否匹配课程类型
       ELSIF (v_card.card_type = 'group' AND p_class_type = 'private') OR (v_card.card_type = 'private' AND p_class_type != 'private') THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '卡类型不匹配课程类型', jsonb_build_object('card_id', p_card_id, 'card_type', v_card.card_type, 'class_type', p_class_type), p_member_id);
       -- 检查团课课时卡课时是否足够
       ELSIF v_card.card_type = 'group' AND v_card.card_category = 'session' AND (v_card.remaining_group_sessions IS NULL OR v_card.remaining_group_sessions <= 0) THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '团课课时不足', jsonb_build_object('card_id', p_card_id, 'remaining_group_sessions', v_card.remaining_group_sessions), p_member_id);
       -- 检查私教课时是否足够
       ELSIF v_card.card_type = 'private' AND (v_card.remaining_private_sessions IS NULL OR v_card.remaining_private_sessions <= 0) THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '私教课时不足', jsonb_build_object('card_id', p_card_id, 'remaining_private_sessions', v_card.remaining_private_sessions), p_member_id);
       -- 检查月卡每日签到次数限制
       ELSIF v_card.card_type = 'group' AND v_card.card_category = 'monthly' AND NOT check_monthly_card_daily_limit(p_member_id, p_card_id, p_check_in_date) THEN
         v_is_extra := true;
         PERFORM log_debug('handle_check_in', '月卡每日签到次数已达上限', jsonb_build_object('card_id', p_card_id, 'card_subtype', v_card.card_subtype), p_member_id);
       ELSE
         v_is_extra := false;
         PERFORM log_debug('handle_check_in', '会员卡有效', jsonb_build_object('card_id', p_card_id, 'card_type', v_card.card_type, 'card_category', v_card.card_category), p_member_id);
       END IF;
     ELSE
       v_is_extra := true;
       PERFORM log_debug('handle_check_in', '未指定会员卡', jsonb_build_object('member_id', p_member_id, 'class_type', p_class_type), p_member_id);
     END IF;
                                                                                                                                                                                                                                                                   +
     -- 插入签到记录                                                                                                                                                                                                                                               +
     INSERT INTO check_ins(                                                                                                                                                                                                                                        +
       member_id,                                                                                                                                                                                                                                                  +
       card_id,                                                                                                                                                                                                                                                    +
       class_type,                                                                                                                                                                                                                                                 +
       check_in_date,                                                                                                                                                                                                                                              +
       trainer_id,                                                                                                                                                                                                                                                 +
       is_1v2,                                                                                                                                                                                                                                                     +
       is_extra,                                                                                                                                                                                                                                                   +
       time_slot,                                                                                                                                                                                                                                                  +
       is_private                                                                                                                                                                                                                                                  +
     )                                                                                                                                                                                                                                                             +
     VALUES (                                                                                                                                                                                                                                                      +
       p_member_id,                                                                                                                                                                                                                                                +
       p_card_id,                                                                                                                                                                                                                                                  +
       v_class_type,                                                                                                                                                                                                                                               +
       p_check_in_date,                                                                                                                                                                                                                                            +
       CASE WHEN v_class_type = 'private' THEN p_trainer_id ELSE NULL END,                                                                                                                                                                                         +
       CASE WHEN v_class_type = 'private' THEN p_is_1v2 ELSE FALSE END,                                                                                                                                                                                            +
       v_is_extra,                                                                                                                                                                                                                                                 +
       CASE                                                                                                                                                                                                                                                        +
         WHEN v_class_type = 'morning' THEN '09:00-10:30'                                                                                                                                                                                                          +
         WHEN v_class_type = 'evening' THEN '17:00-18:30'                                                                                                                                                                                                          +
         WHEN v_class_type = 'private' THEN '10:30-11:30'                                                                                                                                                                                                          +
         ELSE '09:00-10:30'                                                                                                                                                                                                                                        +
       END,                                                                                                                                                                                                                                                        +
       CASE WHEN v_class_type = 'private' THEN TRUE ELSE FALSE END                                                                                                                                                                                                 +
     )                                                                                                                                                                                                                                                             +
     RETURNING id INTO v_check_in_id;                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                   +
     -- 更新会员信息                                                                                                                                                                                                                                               +
     UPDATE members                                                                                                                                                                                                                                                +
     SET                                                                                                                                                                                                                                                           +
       extra_check_ins = CASE WHEN v_is_extra THEN extra_check_ins + 1 ELSE extra_check_ins END,                                                                                                                                                                   +
       last_check_in_date = p_check_in_date                                                                                                                                                                                                                        +
     WHERE id = p_member_id;                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 构建返回结果                                                                                                                                                                                                                                               +
     v_result := jsonb_build_object(                                                                                                                                                                                                                               +
       'success', true,                                                                                                                                                                                                                                            +
       'message', CASE                                                                                                                                                                                                                                             +
         WHEN v_is_extra AND v_class_type IN ('morning', 'evening') THEN '您暂无有效的团课卡，已记录为额外签到'                                                                                                                                                    +
         WHEN v_is_extra AND v_class_type = 'private' THEN '您暂无有效的私教卡，已记录为额外签到'                                                                                                                                                                  +
         ELSE '签到成功'                                                                                                                                                                                                                                           +
       END,                                                                                                                                                                                                                                                        +
       'isExtra', v_is_extra,                                                                                                                                                                                                                                      +
       'checkInId', v_check_in_id                                                                                                                                                                                                                                  +
     );                                                                                                                                                                                                                                                            +
                                                                                                                                                                                                                                                                   +
     RETURN v_result;                                                                                                                                                                                                                                              +
   EXCEPTION WHEN OTHERS THEN                                                                                                                                                                                                                                      +
     -- 记录错误                                                                                                                                                                                                                                                   +
     IF current_setting('app.environment', true) = 'development' THEN                                                                                                                                                                                              +
       INSERT INTO debug_logs (function_name, message, details)                                                                                                                                                                                                    +
       VALUES ('handle_check_in', '签到失败',                                                                                                                                                                                                                      +
         jsonb_build_object(                                                                                                                                                                                                                                       +
           'error', SQLERRM,                                                                                                                                                                                                                                       +
           'member_id', p_member_id,                                                                                                                                                                                                                               +
           'class_type', p_class_type                                                                                                                                                                                                                              +
         )                                                                                                                                                                                                                                                         +
       );                                                                                                                                                                                                                                                          +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 返回错误信息                                                                                                                                                                                                                                               +
     RETURN jsonb_build_object(                                                                                                                                                                                                                                    +
       'success', false,                                                                                                                                                                                                                                           +
       'message', SQLERRM                                                                                                                                                                                                                                          +
     );                                                                                                                                                                                                                                                            +
   END;                                                                                                                                                                                                                                                            +
 END;                                                                                                                                                                                                                                                              +
 $function$                                                                                                                                                                                                                                                        +
 
 CREATE OR REPLACE FUNCTION public.handle_check_in(p_member_id uuid, p_name text, p_email text, p_card_id uuid, p_class_type text, p_check_in_date date, p_trainer_id uuid DEFAULT NULL::uuid, p_is_1v2 boolean DEFAULT false, p_time_slot text DEFAULT NULL::text)+
  RETURNS jsonb                                                                                                                                                                                                                                                    +
  LANGUAGE plpgsql                                                                                                                                                                                                                                                 +
 AS $function$                                                                                                                                                                                                                                                     +
 DECLARE                                                                                                                                                                                                                                                           +
   v_result JSONB;                                                                                                                                                                                                                                                 +
   v_is_extra BOOLEAN;                                                                                                                                                                                                                                             +
   v_check_in_id UUID;                                                                                                                                                                                                                                             +
   v_class_type class_type;                                                                                                                                                                                                                                        +
   v_card RECORD;                                                                                                                                                                                                                                                  +
 BEGIN                                                                                                                                                                                                                                                             +
   -- 转换class_type                                                                                                                                                                                                                                               +
   BEGIN                                                                                                                                                                                                                                                           +
     v_class_type := p_class_type::class_type;                                                                                                                                                                                                                     +
   EXCEPTION WHEN OTHERS THEN                                                                                                                                                                                                                                      +
     RETURN jsonb_build_object(                                                                                                                                                                                                                                    +
       'success', false,                                                                                                                                                                                                                                           +
       'message', '无效的课程类型: ' || p_class_type,                                                                                                                                                                                                              +
       'error', SQLERRM                                                                                                                                                                                                                                            +
     );                                                                                                                                                                                                                                                            +
   END;                                                                                                                                                                                                                                                            +
                                                                                                                                                                                                                                                                   +
   -- 验证时间段                                                                                                                                                                                                                                                   +
   IF p_time_slot IS NULL THEN                                                                                                                                                                                                                                     +
     RETURN jsonb_build_object(                                                                                                                                                                                                                                    +
       'success', false,                                                                                                                                                                                                                                           +
       'message', '请选择有效的时间段',                                                                                                                                                                                                                            +
       'error', 'time_slot_required'                                                                                                                                                                                                                               +
     );                                                                                                                                                                                                                                                            +
   END IF;                                                                                                                                                                                                                                                         +
                                                                                                                                                                                                                                                                   +
   -- 开始事务                                                                                                                                                                                                                                                     +
   BEGIN                                                                                                                                                                                                                                                           +
     -- 检查会员是否存在，不存在则创建                                                                                                                                                                                                                             +
     IF NOT check_member_exists(p_member_id) THEN                                                                                                                                                                                                                  +
       INSERT INTO members(id, name, email, is_new_member)                                                                                                                                                                                                         +
       VALUES (p_member_id, p_name, p_email, true);                                                                                                                                                                                                                +
                                                                                                                                                                                                                                                                   +
       v_is_extra := true;                                                                                                                                                                                                                                         +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 检查是否重复签到（考虑时间段）                                                                                                                                                                                                                             +
     IF check_duplicate_check_in_with_time_slot(p_member_id, p_check_in_date, p_class_type, p_time_slot) THEN                                                                                                                                                      +
       RETURN jsonb_build_object(                                                                                                                                                                                                                                  +
         'success', false,                                                                                                                                                                                                                                         +
         'message', '今天已经在这个时段签到过了',                                                                                                                                                                                                                  +
         'isDuplicate', true                                                                                                                                                                                                                                       +
       );                                                                                                                                                                                                                                                          +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 验证会员卡                                                                                                                                                                                                                                                 +
     IF p_card_id IS NOT NULL THEN                                                                                                                                                                                                                                 +
       -- 获取会员卡信息                                                                                                                                                                                                                                           +
       SELECT * INTO v_card FROM membership_cards WHERE id = p_card_id;                                                                                                                                                                                            +
                                                                                                                                                                                                                                                                   +
       IF NOT FOUND THEN                                                                                                                                                                                                                                           +
         RETURN jsonb_build_object(                                                                                                                                                                                                                                +
           'success', false,                                                                                                                                                                                                                                       +
           'message', '会员卡不存在',                                                                                                                                                                                                                              +
           'error', 'card_not_found'                                                                                                                                                                                                                               +
         );                                                                                                                                                                                                                                                        +
       END IF;                                                                                                                                                                                                                                                     +
                                                                                                                                                                                                                                                                   +
       -- 检查会员卡是否属于该会员                                                                                                                                                                                                                                 +
       IF v_card.member_id != p_member_id THEN                                                                                                                                                                                                                     +
         v_is_extra := true;                                                                                                                                                                                                                                       +
       -- 检查会员卡有效性                                                                                                                                                                                                                                         +
       ELSIF NOT check_card_validity(p_card_id, p_member_id, p_class_type, p_check_in_date) THEN                                                                                                                                                                   +
         v_is_extra := true;                                                                                                                                                                                                                                       +
       -- 检查月卡每日签到次数限制                                                                                                                                                                                                                                 +
       ELSIF v_card.card_type = '团课' AND v_card.card_category = '月卡' AND                                                                                                                                                                                       +
             NOT check_monthly_card_daily_limit(p_member_id, p_card_id, p_check_in_date) THEN                                                                                                                                                                      +
         RETURN jsonb_build_object(                                                                                                                                                                                                                                +
           'success', false,                                                                                                                                                                                                                                       +
           'message', CASE                                                                                                                                                                                                                                         +
             WHEN v_card.card_subtype = '单次月卡' THEN '单次月卡每天最多签到1次'                                                                                                                                                                                  +
             WHEN v_card.card_subtype = '双次月卡' THEN '双次月卡每天最多签到2次'                                                                                                                                                                                  +
             ELSE '月卡每日签到次数已达上限'                                                                                                                                                                                                                       +
           END,                                                                                                                                                                                                                                                    +
           'error', 'monthly_card_limit_exceeded'                                                                                                                                                                                                                  +
         );                                                                                                                                                                                                                                                        +
       ELSE                                                                                                                                                                                                                                                        +
         v_is_extra := false;                                                                                                                                                                                                                                      +
       END IF;                                                                                                                                                                                                                                                     +
     ELSE                                                                                                                                                                                                                                                          +
       v_is_extra := true;                                                                                                                                                                                                                                         +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 插入签到记录                                                                                                                                                                                                                                               +
     INSERT INTO check_ins(                                                                                                                                                                                                                                        +
       member_id,                                                                                                                                                                                                                                                  +
       card_id,                                                                                                                                                                                                                                                    +
       class_type,                                                                                                                                                                                                                                                 +
       check_in_date,                                                                                                                                                                                                                                              +
       trainer_id,                                                                                                                                                                                                                                                 +
       is_1v2,                                                                                                                                                                                                                                                     +
       is_extra,                                                                                                                                                                                                                                                   +
       time_slot,                                                                                                                                                                                                                                                  +
       is_private                                                                                                                                                                                                                                                  +
     )                                                                                                                                                                                                                                                             +
     VALUES (                                                                                                                                                                                                                                                      +
       p_member_id,                                                                                                                                                                                                                                                +
       p_card_id,                                                                                                                                                                                                                                                  +
       v_class_type,                                                                                                                                                                                                                                               +
       p_check_in_date,                                                                                                                                                                                                                                            +
       CASE WHEN v_class_type = 'private' THEN p_trainer_id ELSE NULL END,                                                                                                                                                                                         +
       CASE WHEN v_class_type = 'private' THEN p_is_1v2 ELSE FALSE END,                                                                                                                                                                                            +
       v_is_extra,                                                                                                                                                                                                                                                 +
       p_time_slot,                                                                                                                                                                                                                                                +
       CASE WHEN v_class_type = 'private' THEN TRUE ELSE FALSE END                                                                                                                                                                                                 +
     )                                                                                                                                                                                                                                                             +
     RETURNING id INTO v_check_in_id;                                                                                                                                                                                                                              +
                                                                                                                                                                                                                                                                   +
     -- 更新会员信息                                                                                                                                                                                                                                               +
     UPDATE members                                                                                                                                                                                                                                                +
     SET                                                                                                                                                                                                                                                           +
       extra_check_ins = CASE WHEN v_is_extra THEN extra_check_ins + 1 ELSE extra_check_ins END,                                                                                                                                                                   +
       last_check_in_date = p_check_in_date                                                                                                                                                                                                                        +
     WHERE id = p_member_id;                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 构建返回结果                                                                                                                                                                                                                                               +
     v_result := jsonb_build_object(                                                                                                                                                                                                                               +
       'success', true,                                                                                                                                                                                                                                            +
       'message', CASE                                                                                                                                                                                                                                             +
         WHEN v_is_extra AND v_class_type IN ('morning', 'evening') THEN '您暂无有效的团课卡，已记录为额外签到'                                                                                                                                                    +
         WHEN v_is_extra AND v_class_type = 'private' THEN '您暂无有效的私教卡，已记录为额外签到'                                                                                                                                                                  +
         ELSE '签到成功'                                                                                                                                                                                                                                           +
       END,                                                                                                                                                                                                                                                        +
       'isExtra', v_is_extra,                                                                                                                                                                                                                                      +
       'checkInId', v_check_in_id                                                                                                                                                                                                                                  +
     );                                                                                                                                                                                                                                                            +
                                                                                                                                                                                                                                                                   +
     RETURN v_result;                                                                                                                                                                                                                                              +
   EXCEPTION WHEN OTHERS THEN                                                                                                                                                                                                                                      +
     -- 记录错误                                                                                                                                                                                                                                                   +
     IF current_setting('app.environment', true) = 'development' THEN                                                                                                                                                                                              +
       INSERT INTO debug_logs (function_name, message, details)                                                                                                                                                                                                    +
       VALUES ('handle_check_in', '签到失败',                                                                                                                                                                                                                      +
         jsonb_build_object(                                                                                                                                                                                                                                       +
           'error', SQLERRM,                                                                                                                                                                                                                                       +
           'member_id', p_member_id,                                                                                                                                                                                                                               +
           'class_type', p_class_type                                                                                                                                                                                                                              +
         )                                                                                                                                                                                                                                                         +
       );                                                                                                                                                                                                                                                          +
     END IF;                                                                                                                                                                                                                                                       +
                                                                                                                                                                                                                                                                   +
     -- 返回错误信息                                                                                                                                                                                                                                               +
     RETURN jsonb_build_object(                                                                                                                                                                                                                                    +
       'success', false,                                                                                                                                                                                                                                           +
       'message', SQLERRM                                                                                                                                                                                                                                          +
     );                                                                                                                                                                                                                                                            +
   END;                                                                                                                                                                                                                                                            +
 END;                                                                                                                                                                                                                                                              +
 $function$                                                                                                                                                                                                                                                        +
 
(2 rows)

